package com.basker.pisces.orm.parser;

import java.sql.JDBCType;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.ArrayUtils;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import com.basker.pisces.core.datasource.DbInfo;
import com.basker.pisces.core.meta.IDataField;
import com.basker.pisces.exception.ExceptionFactory;
import com.basker.pisces.orm.data.meta.FieldMeta;
import com.basker.pisces.orm.data.meta.IndexMeta;
import com.basker.pisces.orm.data.meta.TableMeta;
import com.basker.pisces.orm.session.ISqlSession;
import com.basker.pisces.orm.session.WhereCondition;
import com.basker.pisces.utils.NumberUtils;

public class MySqlParser extends AbstractSqlParser {

    private static final Map<JDBCType, String> TYPES = new HashMap<>();
    private static final int DEFAULT_TEXT_LENGHT = 255;
    private static final int DEFAULT_NUMERIC_PRECISION = 10;
    private static final int DEFAULT_NUMERIC_SCALE = 4;

    private static final String LINE_SEPARATOR = System.getProperty("line.separator");

    static {
        TYPES.put(JDBCType.CHAR, "CHAR");
        TYPES.put(JDBCType.VARCHAR, "VARCHAR");
        TYPES.put(JDBCType.NVARCHAR, "NVARCHAR");
        TYPES.put(JDBCType.LONGVARCHAR, "TEXT");
        TYPES.put(JDBCType.CLOB, "LONGTEXT");

        TYPES.put(JDBCType.BINARY, "BLOB");
        TYPES.put(JDBCType.BLOB, "LONGBLOB");
        TYPES.put(JDBCType.LONGVARBINARY, "LONGBLOB");

        TYPES.put(JDBCType.BOOLEAN, "TINYINT");

        TYPES.put(JDBCType.BIT, "BIT");
        TYPES.put(JDBCType.TINYINT, "TINYINT");
        TYPES.put(JDBCType.SMALLINT, "SMALLINT");
        TYPES.put(JDBCType.INTEGER, "INT");
        TYPES.put(JDBCType.BIGINT, "BIGINT");

        TYPES.put(JDBCType.FLOAT, "FLOAT");
        TYPES.put(JDBCType.DOUBLE, "DOUBLE");
        TYPES.put(JDBCType.DECIMAL, "DECIMAL");

        TYPES.put(JDBCType.DATE, "DATETIME");
        TYPES.put(JDBCType.TIME, "TIME");
        TYPES.put(JDBCType.TIMESTAMP, "DATETIME");
    }

    @Override
    public String getAddFieldSql(TableMeta tableMeta, FieldMeta fieldMeta) {
        Assert.notNull(tableMeta, "parameter 'tableMeta' is required");
        Assert.notNull(fieldMeta, "parameter 'fieldMeta' is required");

        String sql = this.getFieldSql(tableMeta, fieldMeta, true);

        List<FieldMeta> fieldMetas = tableMeta.getFieldMetas();
        int index = fieldMetas.indexOf(fieldMeta);
        if (index > 0) {
            FieldMeta preFieldMeta = fieldMetas.get(index - 1);
            sql = new StringBuilder(sql).append(" AFTER ").append(preFieldMeta.getName()).toString();
        }

        return MessageFormat.format("ALTER TABLE {0} ADD {1};", tableMeta.getName(), sql);
    }

    @Override
    public String getCreateIndexSql(TableMeta tableMeta, IndexMeta indexMeta) {
        Assert.notNull(tableMeta, "parameter 'tableMeta' is required");
        Assert.notNull(indexMeta, "parameter 'indexMeta' is required");

        return MessageFormat.format("CREATE INDEX {0} ON {1} ({2});", indexMeta.getName(), tableMeta.getName(),
                String.join(",", indexMeta.getFieldNames()));
    }

    @Override
    public String getCreateSchemaSql(DbInfo dbInfo, ISqlSession sqlSession) {
        Assert.notNull(dbInfo, "parameter 'dbInfo' is required");

        List<Map<String, Object>> result = sqlSession.queryForList(
                "show variables where variable_name in ('character_set_database','collation_database');",
                ArrayUtils.EMPTY_OBJECT_ARRAY);

        String charset = (String) result.get(0).get("Value");
        String collation = (String) result.get(1).get("Value");

        StringBuilder sql = new StringBuilder();

        sql.append(
                MessageFormat.format("CREATE DATABASE IF NOT EXISTS {0} DEFAULT CHARACTER SET {1} DEFAULT COLLATE {2};",
                        dbInfo.getDatabaseName(), charset, collation));
        sql.append(LINE_SEPARATOR);
        sql.append("use ");
        sql.append(dbInfo.getDatabaseName());
        sql.append(";");
        sql.append(LINE_SEPARATOR);
        sql.append(LINE_SEPARATOR);

        return sql.toString();
    }

    @Override
    public String getCreateTableSql(TableMeta tableMeta) {
        Assert.notNull(tableMeta, "parameter 'tableMeta' is required");

        if (tableMeta.getFieldMetas().size() == 0) {
            return null;
        }

        String tableName = tableMeta.getName();

        StringBuilder sql = new StringBuilder();
        sql.append(MessageFormat.format("CREATE TABLE IF NOT EXISTS {0} (", tableName));
        sql.append(LINE_SEPARATOR);

        for (FieldMeta fieldMeta : tableMeta.getFieldMetas()) {
            if (StringUtils.isEmpty(fieldMeta.getName())) {
                continue;
            }

            sql.append(this.getFieldSql(tableMeta, fieldMeta, true));
            sql.append(",");
            sql.append(LINE_SEPARATOR);
        }

        if (tableMeta.isChildTable()) {
            sql.append(this.getFieldSql(tableMeta, tableMeta.getFkFieldMeta(), true));
            sql.append(",");
            sql.append(LINE_SEPARATOR);
        }

        sql.deleteCharAt(sql.length() - LINE_SEPARATOR.length() - 1);
        sql.append(");");

        return sql.toString();
    }

    @Override
    public String getModifyFieldSql(TableMeta tableMeta, FieldMeta fieldMeta) {
        Assert.notNull(tableMeta, "parameter 'tableMeta' is required");
        Assert.notNull(fieldMeta, "parameter 'fieldMeta' is required");

        String sql = this.getFieldSql(tableMeta, fieldMeta, false);

        List<FieldMeta> fieldMetas = tableMeta.getFieldMetas();
        int index = fieldMetas.indexOf(fieldMeta);
        if (index > 0) {
            FieldMeta preFieldMeta = fieldMetas.get(index - 1);
            sql = new StringBuilder(sql).append(" AFTER ").append(preFieldMeta.getName()).toString();
        }

        return MessageFormat.format("ALTER TABLE {0} MODIFY {1};", tableMeta.getName(), sql);
    }

    @Override
    public String getSynFieldSql(TableMeta tableMeta, FieldMeta fieldMeta) {
        Assert.notNull(tableMeta, "parameter 'tableMeta' is required");
        Assert.notNull(fieldMeta, "parameter 'fieldMeta' is required");

        String tableName = tableMeta.getName();
        String fieldName = fieldMeta.getName();

        String proName = MessageFormat.format("add_{0}_{1}", tableName, fieldName);

        StringBuilder sql = new StringBuilder();

        sql.append("DELIMITER //");
        sql.append(LINE_SEPARATOR);
        sql.append("CREATE PROCEDURE " + proName + "()");
        sql.append(LINE_SEPARATOR);
        sql.append("BEGIN");
        sql.append(LINE_SEPARATOR);
        sql.append(MessageFormat.format(
                "IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME=''{0}'' AND TABLE_NAME =''{1}'' AND " +
                        "TABLE_SCHEMA = (SELECT DATABASE()))",
                fieldName, tableName));
        sql.append(LINE_SEPARATOR);
        sql.append("THEN ");
        sql.append(this.getAddFieldSql(tableMeta, fieldMeta));
        sql.append(LINE_SEPARATOR);
        sql.append("ELSE ");
        sql.append(this.getModifyFieldSql(tableMeta, fieldMeta));
        sql.append(LINE_SEPARATOR);
        sql.append("END IF;");
        sql.append(LINE_SEPARATOR);
        sql.append("END;//");
        sql.append(LINE_SEPARATOR);
        sql.append("DELIMITER ;");
        sql.append(LINE_SEPARATOR);
        sql.append("CALL " + proName + "();");
        sql.append(LINE_SEPARATOR);
        sql.append("DROP PROCEDURE " + proName + ";");

        return sql.toString();
    }

    @Override
    public String getSynIndexSql(TableMeta tableMeta, IndexMeta indexMeta) {
        Assert.notNull(tableMeta, "parameter 'tableMeta' is required");
        Assert.notNull(indexMeta, "parameter 'indexMeta' is required");

        String tableName = tableMeta.getName();
        String indexName = indexMeta.getName();

        String proName = MessageFormat.format("add_{0}_{1}", tableName, indexName);

        StringBuilder sql = new StringBuilder();

        sql.append("DELIMITER //");
        sql.append(LINE_SEPARATOR);
        sql.append("CREATE PROCEDURE " + proName + "()");
        sql.append(LINE_SEPARATOR);
        sql.append("BEGIN");
        sql.append(LINE_SEPARATOR);
        sql.append(MessageFormat.format(
                "IF NOT EXISTS(SELECT 1 FROM MYSQL.INNODB_INDEX_STATS WHERE INDEX_NAME=''{0}'' AND TABLE_NAME =''{1}'' AND " +
                        "DATABASE_NAME = (SELECT DATABASE()))",
                indexName, tableName));
        sql.append(LINE_SEPARATOR);
        sql.append("THEN ");
        sql.append(this.getCreateIndexSql(tableMeta, indexMeta));
        sql.append(LINE_SEPARATOR);
        sql.append("END IF;");
        sql.append(LINE_SEPARATOR);
        sql.append("END;//");
        sql.append(LINE_SEPARATOR);
        sql.append("DELIMITER ;");
        sql.append(LINE_SEPARATOR);
        sql.append("CALL " + proName + "();");
        sql.append(LINE_SEPARATOR);
        sql.append("DROP PROCEDURE " + proName + ";");

        return sql.toString();
    }

    @Override
    public List<DBColumnInfo> getTableColumns(TableMeta tableMeta, ISqlSession sqlSession) {
        Assert.notNull(tableMeta, "parameter 'tableMeta' is required");
        Assert.notNull(sqlSession, "parameter 'sqlSession' is required");

        String sql = "SELECT COLUMN_NAME,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,COLUMN_COMMENT "
                + "FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =? AND TABLE_SCHEMA = (SELECT DATABASE())";
        List<Map<String, Object>> list = sqlSession.queryForList(sql, new Object[]{tableMeta.getName()});

        List<DBColumnInfo> cols = new ArrayList<>(list.size());

        for (Map<String, Object> row : list) {
            String name = String.valueOf(row.get("COLUMN_NAME"));

            // 字符串长度
            Integer length = NumberUtils.tryParse(row.get("CHARACTER_MAXIMUM_LENGTH"), Integer.class);
            if (length == null) {
                // 数字精度
                length = NumberUtils.parseInt(row.get("NUMERIC_PRECISION"));
            }

            // 数字小数精度
            Integer scale = NumberUtils.parse(row.get("NUMERIC_SCALE"), Integer.class);

            // 注释
            String comment = String.valueOf(row.get("COLUMN_COMMENT"));

            DBColumnInfo col = new DBColumnInfo(name, length, scale, comment);

            cols.add(col);
        }

        return cols;
    }

    @Override
    public List<DBIndexInfo> getTableIndexes(TableMeta tableMeta, ISqlSession sqlSession) {
        Assert.notNull(tableMeta, "parameter 'tableMeta' is required");
        Assert.notNull(sqlSession, "parameter 'sqlSession' is required");

        String sql = "SELECT INDEX_NAME FROM MYSQL.INNODB_INDEX_STATS "
                + "WHERE DATABASE_NAME = (SELECT DATABASE()) AND TABLE_NAME = ? GROUP BY INDEX_NAME";
        // mysql索引系统表里表名存储的是小写，大写传入会查不到，具体机制尚未确认，猜测和具体数据库的字符比较规则有关
        List<Map<String, Object>> list = sqlSession.queryForList(sql,
                new Object[]{tableMeta.getName().toLowerCase()});

        List<DBIndexInfo> indexes = new ArrayList<>(list.size());

        for (Map<String, Object> row : list) {
            String name = String.valueOf(row.get("INDEX_NAME"));

            DBIndexInfo idx = new DBIndexInfo(name);

            indexes.add(idx);
        }

        return indexes;
    }

    @Override
    public String resolvePageSql(String originalSql, int pageIndex, int pageSize) {
        Assert.hasText(originalSql, "parameter 'originalSql' is required");

        int start = pageSize * (pageIndex - 1);
        return originalSql + " LIMIT " + start + ", " + pageSize;
    }

    @Override
    protected String genSelectSQL(TableMeta rootTableMeta, TableMeta tableMeta, WhereCondition condition) {
        String sql = super.genSelectSQL(rootTableMeta, tableMeta, condition);

        if (condition.isForUpdate()) {
            sql += " FOR UPDATE";
        }

        return sql;
    }

    private String getFieldSql(TableMeta tableMeta, FieldMeta fieldMeta, boolean isAdd) {
        IDataField dataField = fieldMeta.getDataField();

        String name = fieldMeta.getName();
        JDBCType jdbcType = dataField.getJDBCType();
        String dbType = TYPES.get(jdbcType);

        if (dbType == null) {
            throw ExceptionFactory.createRuntimeException("pisces-orm.MySqlParser.getFieldSql-001",
                    "JDBCType {0} is not supported in {1}!", jdbcType, this.getClass().getSimpleName());
        }

        Integer length = dataField.getLength();
        Integer scale = dataField.getScale();

        StringBuilder sql = new StringBuilder();
        sql.append(name);
        sql.append(" ");
        sql.append(dbType);

        if (length == null) {
            if (this.isTextType(dbType)) {
                length = DEFAULT_TEXT_LENGHT;
            } else if (this.isFloatType(dbType)) {
                length = DEFAULT_NUMERIC_PRECISION;
            }
        }

        if (scale == null) {
            if (this.isFloatType(dbType)) {
                scale = DEFAULT_NUMERIC_SCALE;
            }
        }

        if (length != null) {
            sql.append("(");
            sql.append(length);

            if (scale != null) {
                sql.append(",");
                sql.append(scale);
            }

            sql.append(")");
        }

        if (fieldMeta == tableMeta.getPkFieldMeta()) {
            if (isAdd) {
                sql.append(" NOT NULL PRIMARY KEY");
            }
        } else if (fieldMeta == tableMeta.getFkFieldMeta()) {
            sql.append(" NOT NULL");
        } else {
            sql.append(" DEFAULT NULL");
        }

        String comment = fieldMeta.getComment();
        if (!StringUtils.isEmpty(comment)) {
            sql.append(" COMMENT '");
            sql.append(comment);
            sql.append("'");
        }

        return sql.toString();
    }

    private boolean isFloatType(String dbType) {
        return "FLOAT".equalsIgnoreCase(dbType) || "DOUBLE".equalsIgnoreCase(dbType)
                || "DECIMAL".equalsIgnoreCase(dbType);
    }

    private boolean isTextType(String dbType) {
        return "VARCHAR".equalsIgnoreCase(dbType) || "CHAR".equalsIgnoreCase(dbType)
                || "NVARCHAR".equalsIgnoreCase(dbType) || "NCHAR".equals(dbType);
    }

}
